#!/usr/bin/env python
# -*- coding: UTF-8 -*-
#
# Copyright [Gtlions Lai].
# Create Date:
# Update Date:
__authors__ = '"Gtlions Lai" <gtlions.l@qq.com>'

"""Summary about the module or script.


Summary about function.


  Class: about class.
  Function(): about function.
"""

import time
import sys
import platform
import dsn

if platform.system() == 'Windows' or platform.system() == 'Darwin':
    from psycopg2 import *
elif platform.system() == 'Linux':
    from pygresql.pgdb import *
import datetime


def partition_add_months_timestamp(relation, reserve):
    relation = str(relation)
    reserve = int(reserve)
    db = connect(host=dsn.host, port=dsn.port, database=dsn.database, user=dsn.user, password=dsn.password)
    cur = db.cursor()
    cur.execute("select to_char(current_date,'yyyy-mm')")
    partitionrangestart = cur.fetchone()[0]
    cur.execute("select to_char(current_date+interval '1 months','yyyy-mm')")
    partitionrangeend = cur.fetchone()[0]

    cur.execute("select count(*) from pg_tables where schemaname||'.'||tablename=lower('" + relation + "')")
    v_cnt = cur.fetchone()[0]
    if v_cnt:
        pass
    else:
        print 'ERROR: relation "' + relation + '" does not exist'
        return

    if reserve > 0:
        pass
    else:
        print 'ERROR: parameter "i_partition_add_cnt" must greater than 0,but you input ' + str(reserve)
        return

    cur.execute(
        """select pk_name_m,pk_name_m_suffix,p_m_parameter from gpmg.manager_table where schemaname||'.'||tablename=lower('""" + relation + """')""")
    curret = cur.fetchone()
    pk_name = curret[0]
    pk_name_suffix = curret[1]
    p_parameter = curret[2]
    sql_exists_partitions = "select 'alter table " + relation + " add partition '|| partitionname||' start ('||regexp_replace(partitionrangestart,'(numeric|bigint)+','','g')||') end ('||regexp_replace(partitionrangeend,'(numeric|bigint)+','','g')||') " + p_parameter + ";' from pg_partitions where partitionschemaname||'.'||tablename='" + relation + "' and to_date(substr(partitionrangestart,2,11),'yyyy-mm-dd')>='" + partitionrangestart + "-01'::date"
    cur.execute("select '" + pk_name + "'||to_char(current_Date,'" + pk_name_suffix + "')")
    p_name = cur.fetchone()[0]
    sql_next_partitions = ""
    for i in range(1, reserve + 1):
        sql_next_partitions = sql_next_partitions + "select 'alter table " + relation + " add partition " + p_name + " start (''" + partitionrangestart + "-01 00:00:00''::timestamp without time zone) end (''" + partitionrangeend + "-01 00:00:00''::timestamp without time zone) " + p_parameter + ";'"
        if i < reserve:
            sql_next_partitions += " union all "
        cur.execute("select to_char(current_date+interval '" + str(i) + " months' ,'" + pk_name_suffix + "')")
        pk_name_suffix_1 = cur.fetchone()[0]
        p_name = pk_name + pk_name_suffix_1
        cur.execute("select to_char(current_date+interval '" + str(i) + " months' ,'yyyy-mm')")
        partitionrangestart = cur.fetchone()[0]
        cur.execute("select to_char(current_date+interval '" + str(i + 1) + " months' ,'yyyy-mm')")
        partitionrangeend = cur.fetchone()[0]

    cur.execute("select nextval('gpmg.seq_manager_part_mid')")
    manager_part_mid_sid = cur.fetchone()[0]
    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t1) select " + str(
            manager_part_mid_sid) + ",* from (" + sql_exists_partitions + ") a ;")
    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t2) select " + str(
            manager_part_mid_sid) + ",* from (" + sql_next_partitions + ") a ;")
    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t3) select sid,t2 from gpmg.manager_part_mid where sid=" + str(
            manager_part_mid_sid) + " except select sid,t1 from gpmg.manager_part_mid where sid=" + str(
            manager_part_mid_sid) + ";")

    cur.execute("select t3 from gpmg.manager_part_mid where sid=" + str(manager_part_mid_sid) + " and t3 is not null")
    v_msg = "Done.Nothing."
    for record in cur.fetchall():
        sql_add_partitions = record[0]
        cur.execute(sql_add_partitions)
        v_msg = v_msg.replace('Nothing.', '')
    cur.execute("delete from gpmg.manager_part_mid where sid=" + str(manager_part_mid_sid))
    cur.close()
    db.commit()
    db.close()
    return v_msg


def partition_add_day_timestamp(relation, reserve):
    relation = str(relation)
    reserve = int(reserve)
    db = connect(host=dsn.host, database=dsn.database, user=dsn.user, password=dsn.password)
    cur = db.cursor()
    cur.execute("select to_char(current_date,'yyyy-mm-dd hh24:mi:ss')")
    partitionrangestart = cur.fetchone()[0]
    cur.execute("select to_char(current_date+1,'yyyy-mm-dd hh24:mi:ss')")
    partitionrangeend = cur.fetchone()[0]

    cur.execute("select count(*) from pg_tables where schemaname||'.'||tablename=lower('" + relation + "')")
    v_cnt = cur.fetchone()[0]
    if v_cnt:
        pass
    else:
        print 'ERROR: relation "' + relation + '" does not exist'
        return

    if reserve > 0:
        pass
    else:
        print 'ERROR: parameter "i_partition_add_cnt" must greater than 0,but you input ' + str(reserve)
        return

    cur.execute(
        """select pk_name_d,pk_name_d_suffix,p_d_parameter from gpmg.manager_table where schemaname||'.'||tablename=lower('""" + relation + """')""")
    curret = cur.fetchone()
    pk_name = curret[0]
    pk_name_suffix = curret[1]
    p_parameter = curret[2]
    sql_exists_partitions = "select 'alter table " + relation + " add partition '|| partitionname||' start ('||partitionrangestart||') end ('||partitionrangeend||') " + p_parameter + ";' from pg_partitions where partitionschemaname||'.'||tablename='" + relation + "' and to_date(substr(partitionrangestart,2,11),'yyyy-mm-dd')>='" + partitionrangestart + "'::date"
    cur.execute("select '" + pk_name + "'||to_char(current_Date,'" + pk_name_suffix + "')")
    p_name = cur.fetchone()[0]
    sql_next_partitions = ""
    for i in range(1, reserve + 1):
        sql_next_partitions = sql_next_partitions + "select 'alter table " + relation + " add partition " + p_name + " start (''" + partitionrangestart + "''::timestamp without time zone) end (''" + partitionrangeend + "''::timestamp without time zone) " + p_parameter + ";'"

        if i < reserve:
            sql_next_partitions += " union all "
        cur.execute("select to_char(current_date+" + str(i) + ",'" + pk_name_suffix + "')")
        pk_name_suffix_1 = cur.fetchone()[0]
        p_name = pk_name + pk_name_suffix_1
        cur.execute("select to_char(current_date+" + str(i) + ",'yyyy-mm-dd hh24:mi:ss')")
        partitionrangestart = cur.fetchone()[0]
        cur.execute("select to_char(current_date+" + str(i + 1) + ",'yyyy-mm-dd hh24:mi:ss')")
        partitionrangeend = cur.fetchone()[0]

    cur.execute("select nextval('gpmg.seq_manager_part_mid')")
    manager_part_mid_sid = cur.fetchone()[0]
    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t1) select " + str(
            manager_part_mid_sid) + ",* from (" + sql_exists_partitions + ") a ;")
    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t2) select " + str(
            manager_part_mid_sid) + ",* from (" + sql_next_partitions + ") a ;")
    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t3) select sid,t2 from gpmg.manager_part_mid where sid=" + str(
            manager_part_mid_sid) + " except select sid,t1 from gpmg.manager_part_mid where sid=" + str(
            manager_part_mid_sid) + ";")

    cur.execute("select t3 from gpmg.manager_part_mid where sid=" + str(manager_part_mid_sid) + " and t3 is not null")
    v_msg = "Done.Nothing."
    for record in cur.fetchall():
        sql_add_partitions = record[0]
        cur.execute(sql_add_partitions)
        v_msg = v_msg.replace('Nothing.', '')
    cur.execute("delete from gpmg.manager_part_mid where sid=" + str(manager_part_mid_sid))
    cur.close()
    db.commit()
    db.close()
    return v_msg


def partition_add_day_integer(relation, reserve):
    relation = str(relation)
    reserve = int(reserve)
    db = connect(host=dsn.host, database=dsn.database, user=dsn.user, password=dsn.password)
    cur = db.cursor()
    cur.execute("select to_char(current_date,'yyyymmdd')")
    partitionrangestart = cur.fetchone()[0]
    cur.execute("select to_char(current_date+1,'yyyymmdd')")
    partitionrangeend = cur.fetchone()[0]

    cur.execute("select count(*) from pg_tables where schemaname||'.'||tablename=lower('" + relation + "')")
    v_cnt = cur.fetchone()[0]
    if v_cnt:
        pass
    else:
        print 'ERROR: relation "' + relation + '" does not exist'
        return

    if reserve > 0:
        pass
    else:
        print 'ERROR: parameter "i_partition_add_cnt" must greater than 0,but you input ' + str(reserve)
        return

    cur.execute(
        """select pk_name_d,pk_name_d_suffix,p_d_parameter from gpmg.manager_table where schemaname||'.'||tablename=lower('""" + relation + """')""")
    curret = cur.fetchone()
    pk_name = curret[0]
    pk_name_suffix = curret[1]
    p_parameter = curret[2]
    sql_exists_partitions = "select 'alter table " + relation + " add partition '|| partitionname||' start ('||regexp_replace(partitionrangestart,'(::|numeric|bigint)+','','g')||') end ('||regexp_replace(partitionrangeend,'(::|numeric|bigint)+','','g')||') " + p_parameter + ";' from pg_partitions where partitionschemaname||'.'||tablename='" + relation + "' and regexp_replace(partitionrangestart,'(::|numeric|bigint)+','','g')::int>=" + partitionrangestart + " and regexp_replace(partitionrangestart,'(::|numeric|bigint)+','','g')::int<29990101"
    cur.execute("select '" + pk_name + "'||to_char(current_Date,'" + pk_name_suffix + "')")
    p_name = cur.fetchone()[0]
    sql_next_partitions = ""
    for i in range(1, reserve + 1):
        sql_next_partitions = sql_next_partitions + "select 'alter table " + relation + " add partition " + p_name + " start (" + partitionrangestart + ") end (" + partitionrangeend + ") " + p_parameter + ";'"

        if i < reserve:
            sql_next_partitions += " union all "

        cur.execute("select to_char(current_date+" + str(i) + ",'" + pk_name_suffix + "')")
        pk_name_suffix_1 = cur.fetchone()[0]
        p_name = pk_name + pk_name_suffix_1
        cur.execute("select to_char(current_date+" + str(i) + ",'yyyymmdd')")
        partitionrangestart = cur.fetchone()[0]
        cur.execute("select to_char(current_date+" + str(i + 1) + ",'yyyymmdd')")
        partitionrangeend = cur.fetchone()[0]

    cur.execute("select nextval('gpmg.seq_manager_part_mid')")
    manager_part_mid_sid = cur.fetchone()[0]
    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t1) select " + str(
            manager_part_mid_sid) + ",* from (" + sql_exists_partitions + ") a ;")
    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t2) select " + str(
            manager_part_mid_sid) + ",* from (" + sql_next_partitions + ") a ;")
    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t3) select sid,t2 from gpmg.manager_part_mid where sid=" + str(
            manager_part_mid_sid) + " except select sid,t1 from gpmg.manager_part_mid where sid=" + str(
            manager_part_mid_sid) + ";")

    cur.execute("select t3 from gpmg.manager_part_mid where sid=" + str(manager_part_mid_sid) + " and t3 is not null")
    v_msg = "Done.Nothing."
    for record in cur.fetchall():
        sql_add_partitions = record[0]
        cur.execute(sql_add_partitions)
        v_msg = v_msg.replace('Nothing.', '')
    cur.execute("delete from gpmg.manager_part_mid where sid=" + str(manager_part_mid_sid))
    cur.close()
    db.commit()
    db.close()
    return v_msg


def partition_add_hour_timestamp(relation, reserve):
    relation = str(relation)
    reserve = int(reserve)
    db = connect(host=dsn.host, database=dsn.database, user=dsn.user, password=dsn.password)
    cur = db.cursor()
    cur.execute("select count(*) from pg_tables where schemaname||'.'||tablename=lower('" + relation + "')")
    v_cnt = cur.fetchone()[0]
    if v_cnt:
        pass
    else:
        print 'ERROR: relation "' + relation + '" does not exist'
        return

    if reserve > 0:
        pass
    else:
        print 'ERROR: parameter "i_partition_add_cnt" must greater than 0,but you input ' + str(reserve)
        return
    cur.execute("select date_trunc('day',current_date)::timestamp without time zone")
    partitionrangestart = cur.fetchone()[0]
    partitionrangeend = partitionrangestart
    cur.execute(
        """select pk_name_h,pk_name_h_suffix,p_h_parameter from gpmg.manager_table where schemaname||'.'||tablename=lower('""" + relation + """')""")
    curret = cur.fetchone()
    pk_name = curret[0]
    pk_name_suffix = curret[1]
    p_parameter = curret[2]
    cur.execute("select nextval('gpmg.seq_manager_part_mid')")
    manager_part_mid_sid = cur.fetchone()[0]
    cur.execute("insert into gpmg.manager_part_mid(sid,t1) select " + str(
        manager_part_mid_sid) + ",'alter table " + relation + " add partition '|| partitionname||' start ('||partitionrangestart||') end ('||partitionrangeend||')" + p_parameter + ";' from pg_partitions where partitionschemaname||'.'||tablename= '" + relation + "' and substring(partitionrangestart,1,21)::timestamp>=%s",
                (partitionrangestart,))
    cur.execute("insert into gpmg.manager_part_mid(sid,t2) select " + str(
        manager_part_mid_sid) + ",'alter table " + relation + " add partition " + pk_name + "'||a.rq||b.h||' start ('''||a.rqv||' '||b.h||':00:00''::timestamp without time zone) end ('''||(case when b.h=23 then a.rqvn else a.rqv end)||' ' ||(case when b.h=23 then '00' else b.hn end)||':00:00''::timestamp without time zone)' ||'" + p_parameter + ";' from gpmg.manager_date a,gpmg.manager_hour b where a.rqd>=current_date and a.rqd<=current_Date+" + str(
        reserve) + " order by a.id,b.h")

    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t3) select sid,t2 from gpmg.manager_part_mid where sid=" + str(
            manager_part_mid_sid) + " except select sid,t1 from gpmg.manager_part_mid where sid=" + str(
            manager_part_mid_sid) + ";")

    cur.execute("select t3 from gpmg.manager_part_mid where sid=" + str(manager_part_mid_sid) + " and t3 is not null")
    v_msg = "Done.Nothing."
    for record in cur.fetchall():
        sql_add_partitions = record[0]
        cur.execute(sql_add_partitions)
        v_msg = v_msg.replace('Nothing.', '')
    cur.execute("delete from gpmg.manager_part_mid where sid=" + str(manager_part_mid_sid))
    cur.close()
    db.commit()
    db.close()
    return v_msg


def partition_add_day_hour_integer(relation, reserve):
    relation = str(relation)
    reserve = int(reserve)
    db = connect(host=dsn.host, database=dsn.database, user=dsn.user, password=dsn.password)
    cur = db.cursor()
    cur.execute("select count(*) from pg_tables where schemaname||'.'||tablename=lower('" + relation + "')")
    v_cnt = cur.fetchone()[0]
    if v_cnt:
        pass
    else:
        print 'ERROR: relation "' + relation + '" does not exist'
        return

    if reserve > 0:
        pass
    else:
        print 'ERROR: parameter "i_partition_add_cnt" must greater than 0,but you input ' + str(reserve)
        return
    cur.execute(
        """select pk_name_d,pk_name_d_suffix,coalesce(p_d_parameter,' '),pk_name_h,pk_name_h_suffix,coalesce(p_h_parameter,' ') from gpmg.manager_table where schemaname||'.'||tablename=lower('""" + relation + """')""")
    curret = cur.fetchone()
    pk_name_d = curret[0]
    pk_name_d_suffix = curret[1]
    p_d_parameter = curret[2]
    pk_name_h = curret[3]
    pk_name_h_suffix = curret[4]
    p_h_parameter = curret[5]
    cur.execute("select to_char(current_date,'yyyymmdd')")
    partitionrangestart = cur.fetchone()[0]
    cur.execute("select to_char(current_date+1,'yyyymmdd')")
    partitionrangeend = cur.fetchone()[0]
    cur.execute("select nextval('gpmg.seq_manager_part_mid')")
    manager_part_mid_sid = cur.fetchone()[0]
    cur.execute("""insert into gpmg.manager_part_mid(sid,t1)
    select """ + str(manager_part_mid_sid) + """ ,* from
    (with t1 as (select schemaname,tablename,partitionschemaname,partitiontablename,partitionname,parentpartitiontablename,partitionrangestart,partitionrangeend
    from pg_partitions where partitionschemaname||'.'||tablename=lower('""" + relation + """') and regexp_replace(partitionrangestart,'(::|numeric|bigint|smallint)+','','g')::int>=""" + partitionrangestart + """ and regexp_replace(partitionrangestart,'(::|numeric|bigint|smallint)+','','g')::int<29990101 and partitionlevel=0)
    select 'alter table '||lower('""" + relation + """')||' add partition '|| t1.partitionname||' start ('||regexp_replace(t1.partitionrangestart,'(::|numeric|bigint|smallint)+','','g')||') end ('||regexp_replace(t1.partitionrangeend,'(::|numeric|bigint|smallint)+','','g')||') '||'""" + p_d_parameter + """'||' (START (0) END (24) every (1) '||'""" + p_h_parameter + """'||');'  from t1) a ;""")
    cur.execute("select to_char(current_date,'" + pk_name_d_suffix + "')")
    p_name_d = pk_name_d + cur.fetchone()[0]
    sql_next_partitions = ""
    for i in range(1, reserve + 1):
        sql_next_partitions = sql_next_partitions + "select 'alter table '||'" + relation + "'||' add partition '||'" + p_name_d + "'||' start ('||" + partitionrangestart + "||') end ('||" + partitionrangeend + "||') '||'" + p_d_parameter + "'||' (START (0) END (24) every (1) '|| '" + p_h_parameter + "'||');'"
        if i < reserve:
            sql_next_partitions += " union all "

        cur.execute("select to_char(current_date+" + str(i) + ",'" + pk_name_d_suffix + "')")
        pk_name_d_suffix_1 = cur.fetchone()[0]

        p_name_d = pk_name_d + pk_name_d_suffix_1
        cur.execute("select to_char(current_date+" + str(i) + ",'yyyymmdd')")
        partitionrangestart = cur.fetchone()[0]
        cur.execute("select to_char(current_date+" + str(i + 1) + ",'yyyymmdd')")
        partitionrangeend = cur.fetchone()[0]

    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t2) select " + str(
            manager_part_mid_sid) + ",* from (" + sql_next_partitions + ") a ;")
    cur.execute(
        "insert into gpmg.manager_part_mid(sid,t3) select sid,t2 from gpmg.manager_part_mid where sid=" + str(
            manager_part_mid_sid) + " except select sid,t1 from gpmg.manager_part_mid where sid=" + str(
            manager_part_mid_sid) + ";")

    cur.execute("select t3 from gpmg.manager_part_mid where sid=" + str(manager_part_mid_sid) + " and t3 is not null")
    v_msg = "Done.Nothing."
    for record in cur.fetchall():
        sql_add_partitions = record[0]
        cur.execute(sql_add_partitions)
        v_msg = v_msg.replace('Nothing.', '')
    cur.execute("delete from gpmg.manager_part_mid where sid=" + str(manager_part_mid_sid))
    cur.close()
    db.commit()
    db.close()
    return v_msg


def partition_drop():
    db = connect(host=dsn.host, database=dsn.database, user=dsn.user, password=dsn.password)
    cur = db.cursor()
    cur.execute("select  to_char(now(),'yyyymmddhh24miss');")
    log_id = cur.fetchone()[0]
    cur.execute("select schemaname,tablename,partlevel,p_type,pk_name_m,pk_name_d, pk_name_h, pk_name_m_suffix,"
                " pk_name_d_suffix, pk_name_h_suffix, retention_m, retention_d, retention_h from gpmg.manager_table where debug=0 and (status=1 or (status=0 and expire>current_date) ) and (oper_type='all' or oper_type='del') order by id")
    serl = 0
    for ret in cur.fetchall():

        schemaname = ret[0]
        tablename = ret[1]
        partlevel = ret[2]
        p_type = ret[3]
        pk_name_m = ret[4]
        pk_name_d = ret[5]
        pk_name_h = ret[6]
        pk_name_m_suffix = ret[7]
        pk_name_d_suffix = ret[8]
        pk_name_h_suffix = ret[9]
        retention_m = str(ret[10])
        retention_d = str(ret[11])
        retention_h = str(ret[12])
        relation = schemaname + "." + tablename
        serl += 1
        print "|" + str(datetime.datetime.now()).center(25) + "|" + str(serl).center(5) + "|" + relation.center(
            45) + "|"

        cur.execute("select count(*) from pg_tables where schemaname||'.'||tablename=lower('" + relation + "')")
        if not cur.fetchone()[0]:
            print 'ERROR: relation "' + relation + '" does not exist'
            return
        if partlevel == 2:
            if (',' + p_type + ',').find(',d,') >= 0:
                cur.execute(
                    "select '" + pk_name_d + "'||to_char(current_date-" + retention_d + ",'" + pk_name_d_suffix + "')")
                partitionname = cur.fetchone()[0]
                cur.execute(
                    "select count(*) from pg_partitions where schemaname||'.'||tablename='" + relation + "' and partitionname='" + partitionname + "'")
                if cur.fetchone()[0]:
                    cur.execute("alter table " + relation + " drop partition " + partitionname)
                cur.execute(
                    "update gpmg.manager_table set drop_result=now() where schemaname||'.'||tablename='" + relation + "'")
                cur.execute(
                    "insert into gpmg.manager_table_operlog select  '" + schemaname + "','" + tablename + "' , now(),'d'," + log_id)
                db.commit()
        elif partlevel == 1:
            if (',' + p_type + ',').find(',h,') >= 0:
                for i in range(24):
                    cur.execute("select '" + pk_name_h + "'||to_char(current_date-" + retention_h + "+interval '" + str(
                        i) + " hours','yyyymmddhh24')")
                    partitionname = cur.fetchone()[0]
                    cur.execute(
                        "select count(*) from pg_partitions where schemaname||'.'||tablename='" + relation + "' and partitionname='" + partitionname + "'")
                    if cur.fetchone()[0]:
                        cur.execute("alter table " + relation + " drop partition " + partitionname)
                        db.commit()
                cur.execute(
                    "update gpmg.manager_table set drop_result=now() where schemaname||'.'||tablename='" + relation + "'")
                cur.execute(
                    "insert into gpmg.manager_table_operlog select  '" + schemaname + "','" + tablename + "' , now(),'d'," + log_id)
                db.commit()
            if (',' + p_type + ',').find(',d,') >= 0:
                cur.execute(
                    "select '" + pk_name_d + "'||to_char(current_date-" + retention_d + ",'" + pk_name_d_suffix + "')")
                partitionname = cur.fetchone()[0]
                cur.execute(
                    "select count(*) from pg_partitions where schemaname||'.'||tablename='" + relation + "' and partitionname='" + partitionname + "'")
                if cur.fetchone()[0]:
                    cur.execute("alter table " + relation + " drop partition " + partitionname)
                cur.execute(
                    "update gpmg.manager_table set drop_result=now() where schemaname||'.'||tablename='" + relation + "'")
                cur.execute(
                    "insert into gpmg.manager_table_operlog select  '" + schemaname + "','" + tablename + "' , now(),'d'," + log_id)
                db.commit()


def partition_merge(relation):
    db = connect(host=dsn.host, database=dsn.database, user=dsn.user, password=dsn.password)
    cur = db.cursor()
    cur.execute(
        "select schemaname,tablename,tableowner,pk_name,pk_name_suffix,subpartnums,merger_days from gpmg.manager_table_merger where schemaname||'.'||tablename = '" + relation + "'")
    for ret in cur.fetchall():
        schemaname = ret[0]
        tablename = ret[1]
        tableowner = ret[2]
        pk_name = ret[3]
        pk_name_suffix = ret[4]
        subpartnums = ret[5]
        merger_days = ret[6]
        relation_mg = relation + "_mg"
        distributed = ""
        cur.execute("select to_char(current_date+" + str(merger_days) + ",'" + pk_name_suffix + "')")
        parentpartitionname = pk_name + cur.fetchone()[0]
        cur.execute(
            "select count(*) from pg_partitions where schemaname||'.'||tablename='" + relation + "' and parentpartitionname='" + parentpartitionname + "'")
        v_cnt = cur.fetchone()[0]
        if subpartnums != v_cnt:
            pass
        else:
            cur.execute("drop table if exists " + relation_mg)
            cur.execute(
                "create table " + relation_mg + " (like " + relation + ") WITH (APPENDONLY=true, COMPRESSTYPE=quicklz)")
            cur.execute(
                "select schemaname,partitiontablename,partitionposition from pg_partitions where schemaname||'.'||tablename='" + relation + "' and parentpartitionname='" + parentpartitionname + "' order by partitionposition")
            for ret in cur.fetchall():
                subpartitionname = ret[0] + "." + ret[1]
                cur.execute("insert into " + relation_mg + " select * from " + subpartitionname)
            cur.execute("alter table " + relation + " drop partition " + parentpartitionname)
            cur.execute(
                "select 'alter table " + relation + " add partition " + parentpartitionname + " start ('||to_char(current_date+" + str(
                    merger_days) + ",'yyyymmdd')||') end('||to_char(current_date+" + str(
                    merger_days + 1) + ",'yyyymmdd')||')'||coalesce(p_d_parameter,'')||' (SUBPARTITION h start(0) end(24) '||coalesce(p_h_parameter,'')||');' from gpmg.manager_Table where schemaname||'.'||tablename='" + relation + "'")
            ret = cur.fetchone()[0]
            cur.execute(ret)
            cur.execute("select nextval('gpmg.seq_partition_merger')")
            partition_merger_mid_sid = cur.fetchone()[0]

            cur.execute("insert into gpmg.partition_merger_mid(sid,t2) select " + str(
                partition_merger_mid_sid) + ", indexdef from pg_indexes where schemaname||'.'||tablename in (select schemaname||'.'||partitiontablename from pg_partitions where schemaname||'.'||tablename='" + relation + "' and parentpartitionname='" + parentpartitionname + "')")
            cur.execute("insert into gpmg.partition_merger_mid(sid,t1) select " + str(
                partition_merger_mid_sid) + ", 'alter index '||schemaname||'.'||indexname ||' rename to i'||(schemaname||'.'||indexname::text)::regclass::oid||';' from pg_indexes where schemaname||'.'||tablename in (select schemaname||'.'||partitiontablename from pg_partitions where schemaname||'.'||tablename='" + relation + "' and parentpartitionname='" + parentpartitionname + "')")
            cur.execute(
                "alter table " + relation + " alter partition " + parentpartitionname + " exchange partition h with table " + relation_mg)

            cur.execute("select t1 from gpmg.partition_merger_mid where sid=" + str(
                partition_merger_mid_sid) + " and t1 is not null")
            for ret in cur.fetchall():
                cur.execute(ret[0])
            cur.execute("select t2 from gpmg.partition_merger_mid where sid=" + str(
                partition_merger_mid_sid) + " and t2 is not null")
            for ret in cur.fetchall():
                cur.execute(ret[0])

            cur.execute("delete from gpmg.partition_merger_mid where sid=" + str(partition_merger_mid_sid))
            db.commit()
    cur.close()
    db.commit()
    db.close()


if __name__ == "__main__":
    pass
